import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Alper Kokmen
 *
 */
public class JSQLInsert {
	private String dbURL;
	private String dbJDBCDriver;
	private String dbName;
	private String dbUser;
	private String dbPass;
	
	private Connection dbConnection;
	
	private List<String> tables = new ArrayList<String>();
	private List<String> columns = new ArrayList<String>();
	private List<String> values = new ArrayList<String>();
	
	private List<String> queries = new ArrayList<String>();
	
	public JSQLInsert() {
		dbURL = DatabaseProperties.getString("database.url");
		dbJDBCDriver = DatabaseProperties.getString("database.driver");
		dbName = DatabaseProperties.getString("database.name");
		dbUser = DatabaseProperties.getString("database.username");
		dbPass = DatabaseProperties.getString("database.password");
	}
	
	public String getConnectionURL() {
		return dbURL + ";" + "database=" + dbName + ";" + "user=" + dbUser + ";" + "password=" + dbPass + ";";
	}
	
	public Boolean jSQLConnect() {
		try {
			Class.forName(dbJDBCDriver);
			dbConnection = DriverManager.getConnection(getConnectionURL());
			return true;
		} catch (ClassNotFoundException e) {
			System.out.println("JSQLInsert@ERROR: Failed to find the JDBC driver: " + dbJDBCDriver); //$NON-NLS-1$
			return false;
		} catch (SQLException e) {
			System.out.println("JSQLInsert@ERROR: Failed to connect to the database: " + dbURL); //$NON-NLS-1$
			e.printStackTrace();
			return false;
		}
	}
	
	public Boolean jSQLExtract() {
		try {
			ResultSet resultSet;
			Statement statement = dbConnection.createStatement();
			
			resultSet = statement.executeQuery("SELECT name FROM " + dbName + "..sysobjects WHERE type = 'U'"); //$NON-NLS-1$ //$NON-NLS-2$
			while (resultSet.next()) {
				tables.add(resultSet.getString("name")); //$NON-NLS-1$
			}
			
			for (String table : tables) {
				resultSet = statement.executeQuery("SELECT * FROM " + dbName + ".." + table); //$NON-NLS-1$ //$NON-NLS-2$
				
				for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
					int type = resultSet.getMetaData().getColumnType(i); 
					if (type == Types.INTEGER || type == Types.TINYINT) {
						columns.add(resultSet.getMetaData().getColumnName(i));
					} else if (type == Types.VARCHAR || type == Types.CHAR) {
						columns.add("$" + resultSet.getMetaData().getColumnName(i)); //$NON-NLS-1$
					} else if (type == Types.DATE || type == Types.TIMESTAMP) {
						columns.add("@" + resultSet.getMetaData().getColumnName(i)); //$NON-NLS-1$
					}
				}
				
				while (resultSet.next()) {
					String valuesString = ""; //$NON-NLS-1$
					for (int i = 0; i < columns.size(); i++) {
						if (columns.get(i).startsWith("@")) { //$NON-NLS-1$
							valuesString += "'" + resultSet.getString(columns.get(i).substring(1)) + "'"; //$NON-NLS-1$ //$NON-NLS-2$
						} else if (columns.get(i).startsWith("$")) { //$NON-NLS-1$
							valuesString += "'" + resultSet.getString(columns.get(i).substring(1)) + "'"; //$NON-NLS-1$ //$NON-NLS-2$
						} else {
							valuesString += resultSet.getString(columns.get(i));
						}
						valuesString += ","; //$NON-NLS-1$
					}
					valuesString = valuesString.substring(0, valuesString.length() - 1);
					values.add(valuesString);
				}
				
				for (int i = 0; i < columns.size(); i++) {
					if (columns.get(i).startsWith("@")) { //$NON-NLS-1$
						columns.set(i, columns.get(i).substring(1));	
					} else if (columns.get(i).startsWith("$")) { //$NON-NLS-1$
						columns.set(i, columns.get(i).substring(1));	
					}
				}
				
				if (jSQLBuildQueries(table)) {
					
				} else {
					return false;
				}
			}
			
			statement.close();
		} catch (SQLException e) {
			System.out.println("JSQLInsert@ERROR: Failed to get metadata from database."); //$NON-NLS-1$
			e.printStackTrace();
		}
		
		return true;
	}
	
	public Boolean jSQLBuildQueries(String table) {
		String query = "INSERT INTO " + table; //$NON-NLS-1$
		
		String columnsString = "("; //$NON-NLS-1$
		for (String column : columns) {
			columnsString += column + ","; //$NON-NLS-1$
		}
		columnsString = columnsString.substring(0, columnsString.length() - 1);
		columnsString += ")"; //$NON-NLS-1$
		
		query += columnsString;
		
		for (String value : values) {
			String valuesString = "(" + value + ")"; //$NON-NLS-1$ //$NON-NLS-2$
			queries.add(query + " VALUES" + valuesString); //$NON-NLS-1$
		}
		
		queries.add("GO"); //$NON-NLS-1$
		
		return true;
	}
	
	public Boolean jSQLDump(String filename) {
		FileWriter fileWriter;
		BufferedWriter bufferedWriter;
		
		try {
			fileWriter = new FileWriter(filename);
			bufferedWriter = new BufferedWriter(fileWriter);
			
			for (String query : queries) {
				bufferedWriter.write(query);
				bufferedWriter.write("\n"); //$NON-NLS-1$
			}
			
			bufferedWriter.close();
			fileWriter.close();
		} catch (IOException e) {
			System.out.print("JSQLInsert@ERROR: Failed to create the file to dump queries."); //$NON-NLS-1$
			return false;
		}
		
		
		return true;
	}
	
	public Boolean jSQLDisconnect() {
		try {
			dbConnection.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		
		if (args.length != 1) {
			System.out.print("JSQLInsert@ERROR: Argument required - filename to dump queries."); //$NON-NLS-1$
			System.exit(0);
		}
		
		JSQLInsert application = new JSQLInsert();
		
		System.out.print("JSQLInsert@INFO: Connecting to the server:\t\t"); //$NON-NLS-1$
		if (application.jSQLConnect()) {
			System.out.println("[OK]"); //$NON-NLS-1$
			
			System.out.print("JSQLInsert@INFO: Extracting data from the server:\t"); //$NON-NLS-1$
			if (application.jSQLExtract()) {
				System.out.println("[OK]");	 //$NON-NLS-1$
			} else {
				System.out.println("[FAILED]"); //$NON-NLS-1$
			}
			
			System.out.print("JSQLInsert@INFO: Writing queries to the file:\t\t"); //$NON-NLS-1$
			if (application.jSQLDump(args[0])) {
				System.out.println("[OK]");	 //$NON-NLS-1$
			} else {
				System.out.println("[FAILED]"); //$NON-NLS-1$
			}
			
			System.out.print("JSQLInsert@INFO: Disconnecting from the server:\t\t"); //$NON-NLS-1$
			if (application.jSQLDisconnect()) {
				System.out.println("[OK]"); //$NON-NLS-1$
			} else {
				System.out.println("[FAILED]"); //$NON-NLS-1$
			}
		} else {
			System.out.println("[FAILED]"); //$NON-NLS-1$
		}
	}

}
